/*******************************************************************************
Project:		Wealth -- Smith, Zidar, and Zwick
Last modified: 	2021-07-21
Modified by:	Dustin Swonder
Description:	This file builds an SCF microfile to be used broadly for the 
				paper and slides. Wealth and income concepts all in current USD.
*******************************************************************************/

clear
clear mata
clear matrix
set maxvar 6000

/*******************************************************************************
	(0) Pull latest vintage of bulletin data and replicate weights from online;
		 don't need to run this every time.
*******************************************************************************/

/* capture !mkdir $rawdir/bulletin_data_fed2019vintage
cd $rawdir/bulletin_data_fed2019vintage

capture rm README.txt

forv year = 1989(3)2019 {
	copy https://www.federalreserve.gov/econres/files/scfp`year's.zip scfp`year's.zip, replace

	unzipfile scfp`year's.zip, replace 
	rm scfp`year's.zip
}

!echo "Last downloaded $S_DATE $S_TIME by Dustin Swonder" >> README.txt */

/*******************************************************************************
	(1) PULL AUXILIARY DATA: Financial Accounts DB pensions totals, so we can 
		scale down tot_pen_db. As is, SHV subtract L.117 DC pensions from the
		entirety of B.101 pension entitlements and allocate this remainder 
		according to SCF questions about DB pension receipt. However, remainder 
		includes annuities and miscellaneous entitlements, so it's too 
		expansive. Ultimately, we want to allocate L.117 funded or total DB 
		pensions.
*******************************************************************************/

import delim "$datadir/financial_accounts/z1_csv_files_2020Q3/csv/b101.csv", varname(1) clear

keep if regexm(date, ":Q4")
replace date = substr(date, 1, 4)

destring date fl153050005, gen(year pensionentitlements_b101) ignore("ND")

tempfile b101
save `b101'

import delim "$datadir/financial_accounts/z1_csv_files_2020Q3/csv/l117.csv", varname(1) clear

keep if regexm(date, ":Q4")
replace date = substr(date, 1, 4)

destring date fl594190045 fl592000075 fl594090055, gen(year dbtotal dbfunded dc) ignore("ND")

keep year dbtotal dbfunded dc

merge 1:1 year using `b101', keepusing(pensionentitlements_b101) assert(3) nogen

gen db_shv = pensionentitlements_b101 - dc
drop dc pensionentitlements_b101

* Sabelhaus-Henriques Volz concept is at least as big as L.117 item
assert db_shv >= dbtotal

foreach millions of varlist db* {
	replace `millions' = `millions' * 1E6 // Scale from millions to dollars to match SCF
}

tempfile db_usfa
save `db_usfa'

/*******************************************************************************
	(2) Cycling through years, load SCF bulletin files; merge on key variables 
		from full files; and create building block concepts
*******************************************************************************/

forv year = 1989(3)2019 { // cycle through data files
	
	/***************************************************************************
		(2.1) Load in desired variables from summary files
	***************************************************************************/

	use "$rawdir/bulletin_data_fed2019vintage/rscfp`year'.dta", clear

	* Store identifiers in local macros, since they differ
	local unitid = cond(`year' == 1989, "XX1", "YY1")
	local identifier = cond(`year' == 1989, "X1", "Y1")

	if `year' == 1989 {
		rename x1 xx1, upper
	}
	else {
		rename y1 yy1, upper
	}

	#delimit ;
	keep `unitid' `identifier' networth wgt age actbus annuit asset bond bus 
		bussefarminc call cashli ccbal cds checking comutf currpen deq edn_inst equity 
		farmbus futpen gbmutf govtbnd houses income install irakh kginc liq married 
		mmda mmmf mortbnd mrthel nmmf nnresre nonactbus notxbnd obnd odebt obmutf 
		omutf oresre oth_inst othfin othnfin othloc othma penacctwd prepaid resdbt 
		reteq retqliq savbnd saving ssretinc stmutf stocks tfbmutf thrift trusts 
		vehic veh_inst wageinc;
	#delimit cr   

	qui ds `unitid' `identifier' wgt age married, not // Make list of concepts to adj for infl
	local adjlist = "`r(varlist)'"

	qui gen year = `year' // need to create year variable; no such var in raw file

	foreach debtconcept of varlist ccbal edn_inst install mrthel odebt oth_inst othloc ///
		resdbt veh_inst { // Debt concepts are positive

		assert `debtconcept' >= 0
	}

	/***************************************************************************
		(2.2) Get variables in nominal terms for everything; need to undo 
			inflation adjustment for summary file variables, which are all
			in 2019 dollars
	***************************************************************************/

	qui merge m:1 year using $dtadir/scf2019infladjfactors.dta, assert(2 3) keep(3) nogen

	foreach summary_incvar in bussefarminc income kginc ssretinc penacctwd wageinc {
		qui replace `summary_incvar' = `summary_incvar' / incadjfactor

		assert "`adjlist'" != ""
		local adjlist = subinstr("`adjlist'", "`summary_incvar'", "", 1)
	}

	foreach summary_wlthvar of varlist networth actbus annuit asset bond farmbus bus ///
		call cashli ccbal cds checking comutf currpen deq edn_inst equity futpen ///
		gbmutf govtbnd houses install irakh liq mmda mmmf mortbnd mrthel nmmf nnresre ///
		nonactbus notxbnd obnd odebt obmutf omutf oresre oth_inst othfin othnfin ///
		othloc othma prepaid resdbt reteq retqliq savbnd saving stmutf stocks tfbmutf ///
		thrift trusts vehic veh_inst {
		
		qui replace `summary_wlthvar' = `summary_wlthvar' / assetadjfactor

		if "`summary_wlthvar'" != "veh_inst" {
			assert "`adjlist'" != ""
		} 

		local adjlist = subinstr("`adjlist'", "`summary_wlthvar'", "", 1)
	}
	
	drop *adjfactor
	
	local adjlist = subinstr("`adjlist'", " ", "", .)
	assert "`adjlist'" == "" // Ensure we've adjusted all concepts for inflation

	/***************************************************************************
		(2.3) Merge summary file with new variables we've constructed to full 
				raw file to make variables we can't construct without full 
				variables
	***************************************************************************/

	if inlist(`year', 2016, 2019) {
		#delimit ;
		local addtlvars = "X6581 X6582 X6591 X6592 X6551 X6559 X6567 X6552 X6560 
						   X6568 X6553 X6561 X6569 X6554 X6562 X6570 X6756 X6757 
						   X6758 X6556 X6564 X6572 X1306 X1310 X1325 X1329 X1339
						   X7575 X7576 X3453";
		#delimit cr
	}
	else if `year' == 2013 {
		#delimit ;
		local addtlvars = "X6581 X6582 X6591 X6592 X6551 X6559 X6567 X6552 X6560 
						   X6568 X6553 X6561 X6569 X6554 X6562 X6570 X6756 X6757 
						   X6758 X6556 X6564 X6572 X1306 X1310 X1325 X1329 X1339
						   X7575 X7576 X429 X430 X3453";
		#delimit cr
	}
	else if `year' == 2010 {
		#delimit ;
		local addtlvars = "X6581 X6582 X6591 X6592 X6551 X6559 X6567 X6552 X6560 
						   X6568 X6553 X6561 X6569 X6554 X6562 X6570 X6756 X6757 
						   X6758 X6556 X6564 X6572 X1405 X1409 X1505 X1509 X1619
						   X7575 X7576 X429 X430 X3453";
		#delimit cr
	}
	else if inlist(`year', 2004, 2007) {
		#delimit ;
		local addtlvars = "X6581 X6582 X6591 X6592 X6551 X6559 X6567 X6552 X6560 
						   X6568 X6553 X6561 X6569 X6554 X6562 X6570 X6756 X6757 
						   X6758 X6556 X6564 X6572 X1405 X1409 X1505 X1509 X1619
						   X7575 X7576 X423 X424 X429 X430 X3425";
		#delimit cr
	}
	else if inlist(`year', 1998, 2001) {
		#delimit ;
		local addtlvars = "X6826 X6841 X3631 X1405 X1409 X1505 X1509 X1619 X7575 
						   X7576 X423 X424 X429 X430 X3425";
		#delimit cr
	}
	else if inlist(`year', 1992, 1995) {
		#delimit ;
		local addtlvars = "X3947 X3631 X1405 X1409 X1505 X1509 X1619 X423 X424 X429 
						   X430 X7575 X7576 X3425";
		#delimit cr
	}
	else {
		assert `year' == 1989
		local addtlvars = "X3947 X3631 X1405 X1409 X1505 X1509 X1619 X423 X424 X429 X430 X3425"
	}

	local yr = substr("`year'", 3, 4)

	if `year' < 2019 {
		#delimit ;
		qui merge 1:1 `identifier' using "$rawdir/complete_data/p`yr'i6.dta",
			keepusing(X3?19 X3?29 X3?30 X3?24 X3?26 X3?27 X3?21 X3?22 X3335 
					  X3336 X3409 X3413 X3417 X3421 X3429 X3416 X3420 X4020
					  X4022 X4024 X4026 X4028 X4030 X1?03 X1?05 X1?09 X2013 
					  X721 X5702 X5704 X5706 X5708 X5710 X5712 X5714 X5716 
					  X5718 X5724 X5306 X5307 X5311 X5312 X412 X413 X420 X421 
					  X426 X427 `addtlvars')
			assert(3) nogen;
		#delimit cr
	}
	else {
		rename Y1 y1
		local addtlvars = lower("`addtlvars'")

		#delimit ;
		qui merge 1:1 y1 using "$rawdir/complete_data/p`yr'i6.dta",
			keepusing(x3?19 x3?29 x3?30 x3?24 x3?26 x3?27 x3?21 x3?22 x3335 
					  x3336 x3409 x3413 x3417 x3421 x3429 x3416 x3420 x4020
					  x4022 x4024 x4026 x4028 x4030 x1?03 x1?05 x1?09 x2013 
					  x721 x5702 x5704 x5706 x5708 x5710 x5712 x5714 x5716 
					  x5718 x5724 x5306 x5307 x5311 x5312 x412 x413 x420 x421 
					  x426 x427 `addtlvars')
			assert(3) nogen;
		#delimit cr

		rename y1 x*, upper
		local addtlvars = upper("`addtlvars'")
	}

		/***********************************************************************
			(2.3.1) Create/rename variables of interest using full file 
				variables
		***********************************************************************/

			/*******************************************************************
				(2.3.1.1) Rename variables which are good-to-go as-is
			*******************************************************************/

	rename (X721 X5706 X5708 X5710 X5724) (realestatetax intexm intinc divinc othinc)
	qui replace realestatetax = max(0, realestatetax)

			/*******************************************************************
				(2.3.1.2) Generate Social Security income variables from 
					frequency and amount variables re: SS income streams #1 and
					#2
			*******************************************************************/

	* Generate social security income variable
	qui gen ssinc = (X5307 == 3) * (26 * X5306) + (X5307 == 4) * (12 * X5306) + ///
					(X5307 == 5) * (4 * X5306) + (X5307 == 6) * (X5306) + ///
					(X5307 == 12) * (6 * X5306) + /// SS payment stream 1
					(X5312 == 3) * (26 * X5311) + (X5312 == 4) * (12 * X5311) + ///
					(X5312 == 5) * (4 * X5311) + (X5312 == 6) * (X5311) + ///
					(X5312 == 12) * (6 * X5311) // SS payment stream 2

			/*******************************************************************
				(2.3.1.3) Generate an AGI concept following 
					https://www.minneapolisfed.org/research/sr/sr578.pdf, 
					except we'll bottom-censor KGs at -3000 and won't set
					business income to zero if person isn't a soleprop/farm
					owner.
			*******************************************************************/

	qui gen kginc_botcensor = cond(X5712 < - 3000, -3000, X5712)
	assert inrange(kginc / kginc_botcensor, 0.999, 1.001) | ///
		abs(kginc - kginc_botcensor) < 0.01 if X5712 >= -3000

	qui egen agi = rowtotal(X5702 X5704 intinc divinc kginc_botcensor X5714 X5716 X5718 othinc), missing

	drop kginc_botcensor

	/***************************************************************************
		(2.4) Allocate part of other managed assets to equity, and part to 
			equity. For recent years, display annuities and trusts shares (for 
			draft appendix).
	***************************************************************************/

	if `year' >= 2004 {
		qui gen trusts_equshare = cond(X6591 == 1, 1, cond(inlist(X6591, 3, 30), max(0, X6592) / 10000, 0))

		qui summ trusts_equshare [aw = wgt] if trusts > 0 & !missing(trusts), meanonly
	}
	else if `year' >= 1998 {
		qui gen trusts_equshare = cond(X6841 == 1, 1, ///
								  cond(inlist(X6841, 5, 6), 0.5, ///
								  cond(X6841 == -7, 0.3, 0)))
	}

	if `year' >= 2004 {
		qui gen trusts_fixed_sz = max(0, trusts * (1 - trusts_equshare))
		assert abs(trusts - trusts_fixed_sz - (trusts * trusts_equshare) < 10)
	}
	else {
		qui gen trusts_fixed_sz = trusts * 0.5
	}

	drop *_equshare
	
	/***************************************************************************
		(2.5) Retrieve value of businesses by organizational form
	***************************************************************************/

		/***********************************************************************
			(2.5.1) Cycling through actively-managed businesses, store:
				a. whether business owes money to respondent
				b. whether respondent owes money to business
		   (=>) c. value of business
		   		d. value of private C-corporation, if business is ``other 
		   		   corporation (including C chapter corporation)'' 
		***********************************************************************/
	
	local num_active_biz_queried = cond(`year' >= 2010, 2, 3)

	/* Value of business is net worth of share of business plus money owed to 
		respondent from business minus loans from business to respondent plus
		personal assets used as collateral to guarantee loans to business */
	forv busnum = 1 / `num_active_biz_queried' {
		
		qui gen recorded_loan_from_biz`busnum' = X3`busnum'27 == 5
		qui gen recorded_loan_to_biz`busnum' = inlist(X3`busnum'22, 1, 6)
		
		qui gen loan_to_biz`busnum' = max(0, X3`busnum'21)
		qui gen loan_from_biz`busnum' = max(0, X3`busnum'26)

		#delimit ; 
		qui gen actbusw`busnum' = max(0, X3`busnum'29) + max(0, X3`busnum'24) - 
						       max(0, X3`busnum'26) * recorded_loan_from_biz`busnum' +
	        				   loan_to_biz`busnum' * recorded_loan_to_biz`busnum';
	    #delimit cr
	  
	    assert inlist(X3`busnum'19, -7, 0, 1, 2, 3, 4, 6, 11, 12, 40)
	  
	    qui gen actpart`busnum' = cond(X3`busnum'19 == 1, actbusw`busnum', 0)
	    qui gen solepropw`busnum' = cond(inlist(X3`busnum'19, 2, 40), actbusw`busnum', 0)
		qui gen actscorw`busnum' = cond(X3`busnum'19 == 3, actbusw`busnum', 0)
		qui gen actprivccorw`busnum' = cond(X3`busnum'19 == 4, actbusw`busnum', 0)
		qui gen actllc_lp`busnum' = cond(inlist(X3`busnum'19, 11, 12), actbusw`busnum', 0)
		qui gen actother`busnum' = cond(inlist(X3`busnum'19, 6, -7), actbusw`busnum', 0)

		egen actbusw`busnum'_check = rowtotal(actpart`busnum' solepropw`busnum' actscorw`busnum' actprivccorw`busnum' actllc_lp`busnum' actother`busnum')
		assert inrange(actbusw`busnum'_check / actbusw`busnum', 0.999, 1.001) | ///
			abs(actbusw`busnum' - actbusw`busnum'_check) < 10
	}

		/***********************************************************************
			(2.5.2) Quality control: Ensure that total actively-managed private 
				C-corporation value is consistent with actbus bulletin concept 
		***********************************************************************/

	qui egen loans_to_biz = rowtotal(loan_to_biz?)
	qui egen loans_from_biz = rowtotal(loan_from_biz?)

	qui gen actbus_other = max(0, X3335)

	qui egen actbus_check = rowtotal(actbusw? actbus_other farmbus)
	assert inrange(actbus / actbus_check, 0.999, 1.001) | abs(actbus - actbus_check) < 1.01
	drop actbus_check

	qui egen solepropw = rowtotal(solepropw? farmbus)
	qui egen actprivccorw_total = rowtotal(actprivccorw?)
	qui egen actscorw_total = rowtotal(actscorw?)

		/***********************************************************************
			(2.5.3) Retrieve value of non-actively managed private C-corps and 
				create total concept, summing actively and non-actively managed
		***********************************************************************/

	qui gen nonactscorw = max(0, X3416)	
	qui gen nonactprivccorw = max(0, X3420)

	qui egen privccorw = rowtotal(actprivccorw_total nonactprivccorw)
	qui egen scorw = rowtotal(actscorw_total nonactscorw)

	qui gen pthrubus = bus - privccorw - actbus_other
	qui gen partw = pthrubus - scorw - solepropw
	qui gen informalbus = solepropw + nnresre

		/***********************************************************************
			(2.5.4) Allocate unclassifiable biz proportionally to C-corp and 
				pass-through
		***********************************************************************/
	
	qui gen wgt1B = wgt * 1E9

	foreach ttvar of varlist privccorw pthrubus scorw solepropw partw {
		qui summ `ttvar' [fw = wgt1B], meanonly
		local tt`ttvar' = `r(sum)' / 1E9
	}

	local ccorw_share = `ttprivccorw' / (`ttprivccorw' + `ttpthrubus')
	if `year' == 2016 {
		di "C-corporation share pvt biz w/ known org form: " `ccorw_share' * 100
	}

	qui replace privccorw = privccorw + `ccorw_share' * actbus_other
	qui replace pthrubus = pthrubus + (1 - `ccorw_share') * actbus_other

	foreach bizcat in scorw solepropw partw {
		local `bizcat'_share = `tt`bizcat'' / (`ttprivccorw' + `ttpthrubus')
		qui replace `bizcat' = `bizcat' + ``bizcat'_share' * actbus_other
	}
	
	local pthru_share_check = `scorw_share' + `solepropw_share' + `partw_share'
	assert inrange(`pthru_share_check' / (1 - `ccorw_share'), 0.999, 1.001)
	
	qui gen bus_check1 = privccorw + pthrubus 
	qui gen bus_check2 = privccorw + scorw + solepropw + partw 

	forv chknum = 1 / 2 {
		assert inrange(bus / bus_check`chknum', 0.999, 1.001) | abs(bus - bus_check`chknum') < 3
	}
	
		/***********************************************************************
			(2.5.5) Drop columns we no longer need
		***********************************************************************/

	drop bus_check? actbus_other recorded_loan_to_biz? recorded_loan_from_biz? ///
		actbusw? actprivccorw? actprivccorw_total nonactprivccorw actscorw? ///
		actscorw_total nonactscorw solepropw? loan_*_biz? actbusw?_check ///
		actllc_lp? actother? actpart?

	/***************************************************************************
		(2.6) Retrieve value of businesses at cost basis 
	***************************************************************************/
	
		/***********************************************************************
			(2.6.1) Actively managed business
		***********************************************************************/

	forv busnum = 1 / `num_active_biz_queried' { 
		qui gen actbus_costbasis`busnum' = max(0, X3`busnum'30)

		qui gen solepropw_costbasis`busnum' = cond(X3`busnum'19 == 2, actbus_costbasis`busnum', 0)
		qui gen actscorw_costbasis`busnum' = cond(X3`busnum'19 == 3, actbus_costbasis`busnum', 0)
		qui gen actprivccorw_costbasis`busnum' = cond(X3`busnum'19 == 4, actbus_costbasis`busnum', 0)
	}
	
	qui gen actbus_other_costbasis = max(0, X3336)
	qui egen actbus_costbasis = rowtotal(actbus_costbasis? actbus_other_costbasis)

	qui egen solepropw_costbasis = rowtotal(solepropw_costbasis? farmbus)
	qui egen actprivccorw_costbasis_total = rowtotal(actprivccorw_costbasis?)
	qui egen actscorw_costbasis_total = rowtotal(actscorw_costbasis?)
	
		/***********************************************************************
			(2.6.2) Non-actively managed business
		***********************************************************************/
	
	if `year' > 2007 {
		qui gen nonactbus_costbasis = max(0, X3409) + max(0, X3413) + max(0, X3453) + ///
								      max(0, X3417) + max(0, X3421) + max(0, X3429)
	}
	else {
		qui gen nonactbus_costbasis = max(0, X3409) + max(0, X3413) + max(0, X3425) + ///
								      max(0, X3417) + max(0, X3421) + max(0, X3429)
	}
	
	qui gen nonactscorw_costbasis = max(0, X3417)	
	qui gen nonactprivccorw_costbasis = max(0, X3421)

		/***********************************************************************
			(2.6.3) Non-residential real estate assets (we're going to call 
				these informal business assets)
		***********************************************************************/
	
	local num_properties_queried = cond(`year' >= 2010, 2, 3) 

	forv prptynum = 1 / `num_properties_queried' {
		local vardigit = 6 + `prptynum'

		qui gen isnnresre`prptynum' = inlist(X1`vardigit'03, 1, 2, 3, 4, 5, 6, 7) | ///
									   inlist(X1`vardigit'03, 10, 11, 13, 15, 24, 45) | ///
									   inlist(X1`vardigit'03, 46, 47, 48, 51, 53, -7)

		qui gen shareowned`prptynum' = X1`vardigit'05 / 10000
		assert inrange(shareowned`prptynum', 0, 1)

		qui gen nnresre_costbasis`prptynum' = cond(isnnresre`prptynum' == 1, X1`vardigit'09 * shareowned`prptynum', 0)
	}

	qui gen nnresre_costbasis_remaining = max(0, X2013)

	qui egen nnresre_costbasis = rowtotal(nnresre_costbasis? nnresre_costbasis_remaining)
	
		/***********************************************************************
			(2.6.4) Calculate totals, including totals by organizational form of
				business
		***********************************************************************/

	qui egen privccorw_costbasis = rowtotal(actprivccorw_costbasis_total nonactprivccorw_costbasis)
	qui egen scorw_costbasis = rowtotal(actscorw_costbasis_total nonactscorw_costbasis)

	qui gen bus_costbasis = actbus_costbasis + nonactbus_costbasis + farmbus
	drop actbus_costbasis?
 
	qui gen pthrubus_costbasis = bus_costbasis - privccorw_costbasis - actbus_other_costbasis
	qui gen partw_costbasis = pthrubus_costbasis - scorw_costbasis - solepropw_costbasis
	qui gen informalbus_costbasis = solepropw_costbasis + nnresre_costbasis

		/***********************************************************************
			(2.6.5) Allocate unclassifiable biz proportionally to C-corp and 
				pass-through
		***********************************************************************/

	foreach ttvar of varlist privccorw_costbasis pthrubus_costbasis scorw_costbasis ///
		solepropw_costbasis partw_costbasis {

		qui summ `ttvar' [fw = wgt1B], meanonly
		local tt`ttvar' = `r(sum)' / 1E9
	}

	local ccorw_share = `ttprivccorw_costbasis' / (`ttprivccorw_costbasis' + `ttpthrubus_costbasis')
	qui replace privccorw_costbasis = privccorw_costbasis + `ccorw_share' * actbus_other_costbasis
	qui replace pthrubus_costbasis = pthrubus_costbasis + (1 - `ccorw_share') * actbus_other_costbasis

	foreach bizcat in scorw_costbasis solepropw_costbasis partw_costbasis {
		local `bizcat'_share = `tt`bizcat'' / (`ttprivccorw_costbasis' + `ttpthrubus_costbasis')
		qui replace `bizcat' = `bizcat' + ``bizcat'_share' * actbus_other_costbasis
	}
	
	local pthru_share_check = `scorw_costbasis_share' + `solepropw_costbasis_share' + `partw_costbasis_share'
	assert inrange(`pthru_share_check' / (1 - `ccorw_share'), 0.999, 1.001)
	
		/***********************************************************************
			(2.6.6) Drop columns we no longer need
		***********************************************************************/

	drop wgt1B actbus_other_costbasis *_costbasis? *_costbasis_total isnnresre? ///
		shareowned? nnresre_costbasis_remaining nonactprivccorw_costbasis ///
		nonactscorw_costbasis

	/***************************************************************************
		(2.7) Get value of mortgages issued by respondent, which are lumped in 
			with oresre at the moment
	***************************************************************************/
	
	if `year' > 2010 {
		gen mortgageassets = max(X1306, X1310) + max(X1325, X1329) + max(0, X1339)
	}
	else {
		gen mortgageassets = max(X1405, X1409) + max(X1505, X1509) + max(0, X1619)
	}
 
	assert mortgageassets < oresre | abs(oresre - mortgageassets) < 1

	/***************************************************************************
		(2.8) Get value of ``miscellaneous assets'' we want to classify
	***************************************************************************/
	
	forv miscassetnumber = 1 / 3 {
		local classifynum = 4016 + `miscassetnumber' * 4
		local valuenum = 4018 + `miscassetnumber' * 4

		qui gen isdurable`miscassetnumber' = inlist(X`classifynum', 20, 21, 23, 24, 25, 75, 76) | /// 
										 inrange(X`classifynum', 10, 17)
									   	  
		qui gen cash`miscassetnumber' = cond(X`classifynum' == 63, X`valuenum', 0)
		qui gen privloans`miscassetnumber' = cond(inlist(X`classifynum', 61, 62), X`valuenum', 0)
		qui gen durables`miscassetnumber' = cond(isdurable`miscassetnumber' == 1, X`valuenum', 0)
	}

	qui egen cash = rowtotal(cash?)
	qui egen privloans = rowtotal(privloans?)
	qui egen durables = rowtotal(durables?)

	drop cash? privloans? durables? isdurable?

	/***************************************************************************
		(2.9) Get credit card new charges
	***************************************************************************/
	
	if inlist(`year', 2016, 2019) {
		qui gen ccnewcharge = max(0, X426) + max(0, X412) + max(0, X420)
	}
	else if inlist(`year', 2010, 2013) {
		qui gen ccnewcharge = max(0, X426) + max(0, X412) + max(0, X420) + ///
						      max(0, X429)
	}	
	else {
		qui gen ccnewcharge = max(0, X426) + max(0, X412) + max(0, X420) + ///
						      max(0, X429) + max(0, X423)
	}

	/***************************************************************************
		(2.10) Additional income modifications
	***************************************************************************/

	qui gen peninc = max(0, ssretinc - ssinc)
	qui replace wageinc = 0 if wageinc < 0

	/***************************************************************************
		(2.11) Save as tempfile
	***************************************************************************/

	tempfile file`year'
	qui save `file`year''
}

/*******************************************************************************
	(3) Append data together and clean up a bit
*******************************************************************************/

clear

forv year = 1989(3)2019 {
	append using `file`year''
}

* Make YY1 and Y1 into main identifiers if year is 1989 (identifiers = XX1, X1)
assert missing(YY1) & missing(Y1) & !missing(XX1) & !missing(X1) if year == 1989
qui replace YY1 = XX1 if year == 1989
qui replace Y1 = X1 if year == 1989
drop X*

compress

save $dumpdir/scf_nodb_noformatting20210513.dta, replace

/*******************************************************************************
	(4) Get DB pensions: this will be Sabelhaus-Henriques Volz data, and we'll
		make versions which scale down to exclude annuities and the unfunded
		portion of DB
*******************************************************************************/

	/***************************************************************************
		(4.1) Merge on Sabelhaus-Henriques Volz pensions and create net total DB
			pensions measure and new net worth measures including DB. Display 
			2016 total of each of Sabelhaus-Henriques Volz variables for draft 
			appendix.
	***************************************************************************/

rename Y1 y1 // for merge

merge 1:1 y1 year using "$litroot/sabelhaus/DBwealthFiles2019update/db.wealth/DB_household092820.dta", ///
	assert(3) keepusing(*_dbamt_*) nogen
rename y1 Y1

qui egen tot_pen_db = rowtotal(*_dbamt_*)

#delimit ;
gen tot_pen_db_check = currec_pv_dbamt_rtot + currec_pv_dbamt_sptot + 
					   future_pv_dbamt_rtot + future_pv_dbamt_sptot + 
					   curjob_pv_dbamt_r + curjob_pv_dbamt_sp;
#delimit cr
assert abs(tot_pen_db - tot_pen_db_check) <= 1
drop tot_pen_db_check

foreach dbvar of varlist *_dbamt_* {
	qui summ `dbvar' [aw = wgt] if year == 2016, meanonly 

	di "2016 value `dbvar' (trillions): " `r(sum)' / 1E12
}
qui drop *_dbamt_*

	/***************************************************************************
		(4.2) Merge on Financial Accounts totals for scaling down SHV aggregates
			which erroneously include annuities sold directly to consumers by
			life insurance companies
	***************************************************************************/

merge m:1 year using `db_usfa', assert(2 3) keep(3) nogen

sort year
gen tot_pen_db_x_wgt = tot_pen_db * wgt
by year: egen agg_db_shv = total(tot_pen_db_x_wgt)

/* Check that the aggregate DB concept allocated by SHV matches the same aggregate
	when we pull directly from the Financial Accounts; we allow this to differ a 
	bit b/c SHV pull a different USFA vintage than 2020Q3 */
assert inrange(agg_db_shv / db_shv, 0.97, 1.03)

gen shv_db_ratio_to_l117total = dbtotal / agg_db_shv
gen shv_db_ratio_to_l117funded = dbfunded / agg_db_shv

rename tot_pen_db tot_pen_db_sabelhaus_orig
gen tot_pen_db = tot_pen_db_sabelhaus_orig * shv_db_ratio_to_l117total
gen funded_pen_db = tot_pen_db_sabelhaus_orig * shv_db_ratio_to_l117funded

drop db_shv shv_db_ratio_* dbtotal dbfunded agg_db_shv tot_pen_db_x_wgt

/*******************************************************************************
	(5) Make main portfolio categories
*******************************************************************************/

assert veh_inst >= 0

* Preferred net worth concept
qui gen networth_pref = networth + tot_pen_db - (vehic - veh_inst) - durables

qui gen trusts_equity = trusts - trusts_fixed_sz

    /***************************************************************************
        (5.1) Taxonomize wealth in flows-based scheme, putting off trusts 
        	allocation for now
    ***************************************************************************/

qui gen half_omutf = omutf / 2
qui gen half_comutf = comutf / 2

qui gen currency = checking + prepaid + cash

qui gen deposits = saving + cds + mmda // Following DFA definition

qui gen inttaxw_excl_trusts = deposits + call + savbnd + (bond - notxbnd) + ///
				  privloans + mortgageassets

qui gen intexmw_excl_trusts = notxbnd + tfbmutf

* Sometimes we separately break out C-corporation wealth and fixed income mutual funds
qui gen ccorw = stocks + privccorw + stmutf + half_comutf + trusts_equity + half_omutf
qui gen fixmutf_excl_fixtrusts = half_comutf + half_omutf + gbmutf + obmutf + mmmf

qui gen ccorw_mutf_excl_fixtrusts = ccorw + fixmutf_excl_fixtrusts

qui gen pthru = pthrubus + nnresre
qui gen hwpen = annuit + cashli + retqliq + tot_pen_db
qui gen hwhou = houses + (oresre - mortgageassets) - mrthel - resdbt
qui gen othdebt = - othloc - ccbal - edn_inst - oth_inst - odebt 
qui gen hwoth = (othfin - cash - privloans) + (othnfin - durables)

    /***************************************************************************
        (5.2) Taxonomize wealth in risk-based scheme, putting off trusts 
        	allocation for now
    ***************************************************************************/

qui gen hwfix_excl_trusts = currency + inttaxw_excl_trusts + intexmw_excl_trusts + ///
							half_comutf + gbmutf + obmutf + mmmf + half_omutf

qui gen hwbus = bus + nnresre
gen hwbus_check = pthru + privccorw
assert inrange(hwbus_check / hwbus, 0.999, 1.001) | abs(hwbus - hwbus_check) < 0.01
drop hwbus_check

qui gen hwequ = stocks + stmutf + half_comutf + trusts_equity + half_omutf

    /***************************************************************************
        (5.3) Allocate fixed claims held in trusts according to distribution of 
        	other assets
    ***************************************************************************/

	    /***********************************************************************
	        (5.3.1) Create a ``denominator'' variable which is income-generating
	        	components of the risk-based hwfix concept; calculate aggregate
	        	share of this denominator in dividend-generating fixed claims, 
	        	taxable interest-generating fixed claims, and tax-exempt fixed 
	        	claims.
	    ***********************************************************************/

gen wgt1B = wgt * 1E9

qui gen trustsdenom = hwfix_excl_trusts - currency

qui gen dividndgenerating_hwfix = half_comutf + gbmutf + obmutf + mmmf + half_omutf

tempfile orig
save `orig'

collapse (sum) dividndgenerating_hwfix inttaxw_excl_trusts intexmw_excl_trusts ///
	trustsdenom [fw = wgt1B], by(year)

qui gen aggdivshare_trusts = dividndgenerating_hwfix / trustsdenom
qui gen agginttaxshare_trusts = inttaxw_excl_trusts / trustsdenom
qui gen aggintexmshare_trusts = intexmw_excl_trusts / trustsdenom

gen total_check = aggdivshare_trusts + agginttaxshare_trusts + aggintexmshare_trusts
assert inrange(total_check, 0.999, 1.001)

drop total_check

tempfile aggshares
save `aggshares'

	    /***********************************************************************
	        (5.3.2) Merge aggregate shares onto main data set; compute decomp
	        	at the micro level and sub in aggregate shares if denominator is 
	        	zero
	    ***********************************************************************/

use `orig', clear

merge m:1 year using `aggshares', assert(3) nogen keepusing(agg*share_trusts)

qui gen divshare_trusts = cond(trustsdenom < 1, aggdivshare_trusts, dividndgenerating_hwfix / trustsdenom)
qui gen inttaxshare_trusts = cond(trustsdenom < 1, agginttaxshare_trusts, inttaxw_excl_trusts / trustsdenom)
qui gen intexmshare_trusts = cond(trustsdenom < 1, aggintexmshare_trusts, intexmw / trustsdenom)

assert abs(divshare_trusts + inttaxshare_trusts + intexmshare_trusts - 1) < 0.01 if trustsdenom > 16

	    /***********************************************************************
	        (5.3.3) Allocate trusts to different categories
	    ***********************************************************************/

gen trusts_mmbondfund = divshare_trusts * trusts_fixed_sz
gen trusts_inttaxw = inttaxshare_trusts * trusts_fixed_sz
gen trusts_intexmw = intexmshare_trusts * trusts_fixed_sz

gen trusts_check = trusts_equity + trusts_mmbondfund + trusts_inttaxw + trusts_intexmw
assert inrange(trusts_check / trusts, 0.999, 1.001) | abs(trusts - trusts_check) < 1

drop trusts_check *share_trusts trustsdenom dividndgenerating_hwfix

	    /***********************************************************************
	        (5.3.4) Finalize main portfolio categories with trusts
	    ***********************************************************************/

gen inttaxw = inttaxw_excl_trusts + trusts_inttaxw
gen intexmw = intexmw_excl_trusts + trusts_intexmw

gen fixmutf = fixmutf_excl_fixtrusts + trusts_mmbondfund
gen ccorw_mutf = ccorw_mutf_excl_fixtrusts + trusts_mmbondfund

qui gen hwfix = hwfix_excl_trusts + trusts_inttaxw + trusts_mmbondfund + trusts_intexmw

drop *_excl_trusts *_excl_fixtrusts

    /***************************************************************************
        (5.4) Check that taxonomies are mutually exclusive and collectively 
        	exhaustive across assets we want to include
    ***************************************************************************/

qui gen networth_pref_check1 = currency + inttaxw + intexmw + ccorw_mutf + pthru + ///
                               hwpen + hwhou + othdebt + hwoth

qui gen networth_pref_check2 = currency + inttaxw + intexmw + fixmutf + ccorw + ///
                               pthru + hwpen + hwhou + othdebt + hwoth

qui gen networth_pref_check3 = hwfix + hwequ + hwhou + hwbus + hwpen + hwoth + othdebt
qui gen networth_pref_check4 = hwfix + ccorw + hwhou + pthru + hwpen + hwoth + othdebt

forv checknum = 1 / 4 {
	assert inrange(networth_pref / networth_pref_check`checknum', 0.999, 1.001) | ///
	       abs(networth_pref - networth_pref_check`checknum') <= 1.001
}

drop networth_pref_check? half_*mutf 

/*******************************************************************************
	(6) Define non-interest wealth category
*******************************************************************************/

* Conceptually equivalent to hweal112 - taxbond - taxbond_muf in the tax data
gen niw = networth_pref - inttaxw - fixmutf

/*******************************************************************************
	(7) Clean up, label, sort, and save data
*******************************************************************************/

capture drop __000* // get rid of tempvar residue if present

qui ds year YY1 Y1 wgt wgt1B networth niw, not
order `r(varlist)', alphabetic last
order year YY1 Y1 wgt wgt1B networth niw

sort year YY1 Y1

lab var actbus "Actively managed business (bulletin; market value)"
lab var actbus_costbasis "Actively managed business (bulletin adapted, cost basis)"
lab var age "Age of the household head (top-coded 95, bot-coded 17)"
lab var agi "Adjusted Gross Income (approximation)"
lab var annuit "Annuities"
lab var asset "Total Assets from SCF summary files"
lab var bond "Total Bonds, Excl. Bond Funds and Savings Bonds"
lab var bus "Business Interests (bulletin; market value)"
lab var bus_costbasis "Business Interests (bulletin adapted, cost basis)"
lab var bussefarminc "Business/farm income"
lab var call "Call accounts"
lab var cash "Cash n.e.c."
lab var cashli "Cash Value of Whole Life Insurance"
lab var ccbal "Credit Card Balance (After Bill Payment)"
lab var ccnewcharge "Credit Card New Charges"
lab var ccorw_mutf "C-corporation equity and mutual funds (flows-based taxonomy)"
lab var cds "CDs"
lab var checking "Checking accounts (excl. money market)"
lab var comutf "Combination and Other Mutual Funds"
lab var currency "Non income-generating assets (flows-based taxonomy)"
lab var currpen "Currently received account-type pensions"
lab var deposits "Savings accounts, money market deposit accounts, and CDs"
lab var deq "Equity in directly-held stocks, stock mutual funds, comutf, and omutf"
lab var durables "Durables other than vehicles"
lab var edn_inst "Education installment loans"
lab var equity "Equity bulletin concept"
lab var farmbus "Value of farmland associated with primary residence"
lab var fixmutf "[not bulletin] Fixed income mutual funds"
lab var funded_pen_db "Sabelhaus-Henriques Volz DB pensions scaled to match USFA funded DB (L.117)"
lab var futpen "Future pensions (quasi-liquid retirement)"
lab var gbmutf "Government Bond Mutual Funds"
lab var govtbnd "Government and government agency bonds"
lab var houses "Value of Primary Residence"
lab var hwbus "Private business (risk-based taxonomy)"
lab var hwequ "C-corporation equity (risk-based taxonomy)"
lab var hwfix "Fixed claims net of non-residential debt (risk-based taxonomy)"
lab var hwhou "Housing assets (risk- AND flows-based taxonomy)"
lab var hwoth "Miscellaneous assets net (risk- AND flows-based taxonomy)"
lab var hwpen "Pension assets (risk- AND flows-based taxonomies)"
lab var income "Total Income"
lab var informalbus "Informal biz (sole prop + nnresre), market value"
lab var informalbus_costbasis "Informal biz (sole prop + nnresre), cost basis"
lab var install "Other Installment Debt"
lab var intexm "Non-taxed Interest Income"
lab var intexmw "Tax-exempt interest-generating fixed claims (flows-based taxonomy)"
lab var intinc "Taxable Interest Income"
lab var inttaxw "Taxable interest-generating fixed claims (flows-based taxonomy)"
lab var irakh "IRAs/Keoghs"
lab var kginc "Income from capital gains"
lab var liq "Total Liquid Assets from SCF summary files"
lab var loans_from_biz "Loans from HH-owned actively mgd biz to HH"
lab var loans_from_biz "Loans from HH to HH-owned actively mgd biz"
lab var married "=1 married/living w partner =2 not married/living w partner"
lab var mmda "Money market deposit accounts"
lab var mmmf "Money market mutual funds"
lab var mortbnd "Mortgage-backed bond"
lab var mortgageassets "Mortgages held as assets"
lab var mrthel "Debt Secured by Primary Real Estate"
lab var networth "Net worth from SCF summary files"
lab var networth_pref "Bulletin net worth minus durables (net of loans) plus DB pensions"
lab var niw "Preferred net worth minus risk-based fixed income concept"
lab var nmmf "Total Directly-Held Mutual Funds, Excl. MMMFs"
lab var nnresre "Non-residential real estate (bulletin; market value)"
lab var nnresre_costbasis "Non-residential real estate (bulletin adapted, orig price)"
lab var nonactbus "Business not actively managed (bulletin; market value)"
lab var nonactbus_costbasis "Business not actively managed (bulletin adapted, cost basis)"
lab var pthru "Pass-through (flows-based taxonomies)"
lab var notxbnd "Tax-exempt bonds (state + local bonds)"
lab var obmutf "Other bond mutual funds"
lab var obnd "Corporate and foreign bonds"
lab var odebt "Other debt"
lab var omutf "Other mutual funds"
lab var oresre "Other residential real estate"
lab var oth_inst "Other installment loans"
lab var othdebt "Non-residential debt (flows-based taxonomy)"
lab var othfin "Other financial assets"
lab var othloc "Other lines of credit not secured by res real estate"
lab var othma "Other managed assets (Annuities + Trusts)"
lab var othnfin "Other non-financial assets"
lab var partw "Partnership wealth (market value)"
lab var partw_costbasis "Partnership wealth (cost basis)"
lab var penacctwd "Pension account withdrawal (DC income)"
lab var peninc "Pension Income, incl. pen acct withdrawal"
lab var prepaid "Prepaid cards"
lab var privccorw "Private C-corporation wealth (market value)"
lab var privccorw_costbasis "Private C-corporation wealth (cost basis)"
lab var privloans "Loans to friends/family; other loans owed to respondent"
lab var pthrubus "Pass-through busin (S-corps, pships, and informal bus; mkt val)"
lab var pthrubus_costbasis "Pass-through busin (S-corps, pships, and informal bus; cost basis)"
lab var realestatetax "Taxes paid on real estate"
lab var resdbt "Other Residential Debt"
lab var reteq "Equity in quasi-liquid retirement assets"
lab var retqliq "Total quasi-liquid: IRAs, thrift, future pensions"
lab var savbnd "Savings Bonds"
lab var saving "Savings account balance not in MMA or sweep accounts"
lab var scorw "S-corporation wealth (market value)"
lab var scorw_costbasis "S-corporation wealth (cost basis)"
lab var solepropw "Sole prop wealth (incl farmbus concept; market value)"
lab var solepropw_costbasis "Sole prop wealth (incl farmbus concept; cost basis)"
lab var ssretinc "Social Security and retirement income, incl pension acct withdrawals"
lab var ssinc "Social Security Income"
lab var stmutf "Stock Mutual Funds"
lab var stocks "Stocks"
lab var tfbmutf "Tax-free Bond Mutual Funds"
lab var thrift "Account-type pensions on current job"
lab var tot_pen_db_sabelhaus_orig "ORIGINAL Sabelhaus-Henriques Volz DB pensions"
lab var tot_pen_db "[preferred] Sabelhaus-Henriques Volz DB pensions scaled to match USFA DB (L.117)"
lab var trusts "Trusts"
lab var trusts_fixed "Trusts invested in fixed claims (assumed 50% pre-1998)"
lab var trusts_fixed_sz "Trusts invested in fixed claims (assumed 50% pre-2004)"
lab var trusts_equity "Trusts invested in equity (trusts - trusts_fixed_sz)"
lab var trusts_mmbondfund "(Estimate) Trusts invested in dividend-generating fixed claims"
lab var trusts_inttaxw	"(Estimate) Trusts invested in taxable interest-generating fixed claims"
lab var trusts_intexmw "(Estimate) Trusts invested in tax-exempt interest-generating fixedd claims"
lab var veh_inst "Vehicle loans"
lab var vehic "Value of Vehicles"
lab var wgt1B "Weight times one billion"
lab var wageinc "Wage income"

save $dtadir/scf_revision.dta, replace